package com.tsj.model.utils;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

/**
 * @author taoshujian
 * @version 1.0
 * @since 2025-10-25 22:35:19
 */
public class ExcelTools {

    /**
     * 获取单元格的实际值（处理不同数据类型）
     */
    public static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }

        String value;
        switch (cell.getCellType()) {
            case STRING:
                value = cell.getStringCellValue();
                break;
            case NUMERIC:
                // 处理日期类型
                if (DateUtil.isCellDateFormatted(cell)) {
                    value = cell.getDateCellValue().toString();
                } else {
                    // 数字避免科学计数法
                    value = String.valueOf(cell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA:
                // 公式单元格取计算结果
                value = cell.getCellFormula();
                break;
            default:
                value = "";
        }
        return value;
    }

    public static boolean verifyString(Cell cell){
        if (cell == null) {
            return false;
        }
        if (cell.getCellType().equals(CellType._NONE) || cell.getCellType().equals(CellType.BLANK)) {
            return false;
        }
        if(!cell.getCellType().equals(CellType.STRING) && !cell.getCellType().equals(CellType.NUMERIC)){
            return false;
        }
        String v = getCellValueAsString(cell);
        return !v.isEmpty();
    }
    public static boolean verifyDate(Cell cell){
        if (cell.getCellType().equals(CellType._NONE) || cell.getCellType().equals(CellType.BLANK) || !cell.getCellType().equals(CellType.NUMERIC) ||
                !DateUtil.isCellDateFormatted(cell) || cell.getDateCellValue() == null) {
            return false;
        }
        return true;
    }

    public static boolean verifyNumeric(Cell cell){
        if (cell.getCellType().equals(CellType._NONE) || cell.getCellType().equals(CellType.BLANK) || !cell.getCellType().equals(CellType.NUMERIC) ||
                cell.getNumericCellValue() <= 0) {
            return false;
        }
        return true;
    }

    public static String getCellValueAsString(Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                // 判断是否为日期格式
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    // 数值类型转换为字符串
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }

    // 从MIME类型获取格式
    public static String getFormatFromMimeType(String mimeType) {
        if (mimeType == null) return "unknown";
        switch (mimeType) {
            case "image/jpeg", "image/jpg": return "jpg";
            case "image/png": return "png";
            case "image/gif": return "gif";
            case "image/bmp": return "bmp";
            case "image/svg+xml": return "svg";
            default: return mimeType.split("/")[1];
        }
    }

    // 转换单元格地址（行号、列号 → A1格式）
    public static String getCellAddress(int rowIndex, int colIndex) {
        return getColumnLetter(colIndex) + (rowIndex + 1);
    }

    public static String getColumnLetter(int colIndex) {
        if (colIndex < 0) return "";
        StringBuilder sb = new StringBuilder();
        while (colIndex >= 0) {
            int remainder = colIndex % 26;
            sb.append((char) ('A' + remainder));
            colIndex = (colIndex / 26) - 1;
            if (colIndex < 0) break;
        }
        return sb.reverse().toString();
    }

    /**
     * 根据附件的锚点坐标计算其主要关联的单元格地址（如"A1"）
     * 规则：取附件左上角所在的单元格
     */
    public static String getCellAddressByAnchor(XSSFClientAnchor anchor) {
        // 锚点的起始行和起始列（左上角位置）
        int rowIndex = anchor.getRow1();
        int colIndex = anchor.getCol1();

        // 转换列号为字母（如0→A，1→B...）
        String colLetter = ExcelTools.getColumnLetter(colIndex);
        // 行号从1开始（Excel中行号从1起始）
        int rowNumber = rowIndex + 1;

        return colLetter + rowNumber;
    }

    public static void saveToFile(byte[] data, String outputDir, String fileName) {
        try {
            File dir = new File(outputDir);
            if (!dir.exists()) dir.mkdirs();

            File outputFile = new File(dir, fileName);
            try (FileOutputStream fos = new FileOutputStream(outputFile)) {
                fos.write(data);
            }
        } catch (IOException e) {
            System.err.println("保存文件失败: " + fileName);
            e.printStackTrace();
        }
    }

}
